package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.common.WebContext;
import cn.ztydata.teachingsystem.heaven.cons.Roles;
import cn.ztydata.teachingsystem.heaven.entity.CourseGroup;
import cn.ztydata.teachingsystem.heaven.entity.Teacher;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 课程群组Dao类
 *
 * @author lyj
 * @since 2015-3-19
 */
@Repository
public class CourseGroupDao extends BaseDao {
    @Autowired
    private WebContext webContext;

    /**
     * 课程群组详细
     * @param groupId 群组编号
     * @return CourseGroup
     *
     * @author fanruiping
     * @since 2015-03-26
     */
    public CourseGroup getById(Long groupId){
        try{
            String sql = "SELECT id, sCode, sName, sDeptNum From t_course_group WHERE id=:id";

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", groupId), new RowMapper<CourseGroup>() {
                @Override
                public CourseGroup mapRow(ResultSet rs, int i) throws SQLException {
                    CourseGroup group = new CourseGroup();

                    group.setId(rs.getLong("id"));
                    group.setCode(rs.getString("sCode"));
                    group.setName(rs.getString("sName"));
                    group.setDeptNum(rs.getString("sDeptNum"));

                    return group;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("未获取到指定课程群组信息:EmptyResultDataAccessException ex");

            return null;
        }
    }

    /**
     * 根据编号集合获取课程群组名称集合
     * @param ids 编号集合
     * @return Map
     *
     * @author fanruiping
     * @since 2015-05-07
     */
    public Map<Long,String> getNamesByIds(Set<Long> ids){
        final Map<Long,String> result = new HashMap<>();

        String sql = "SELECT id,sName FROM t_course_group WHERE id IN(:ids)";

        namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("ids", ids), new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                result.put(resultSet.getLong("id"), resultSet.getString("sName"));

                return null;
            }
        });

        return result;
    }

    /**
     * 获取当前课程群组Id、群组名称、课程代码、系部代码
     *
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @param keyWord 关键字
     * @param role 角色（0老师 1学生）
     * @return List<CourseGroup>
     * @author lyj
     * @since 2015/3/30
     */

    public Page<CourseGroup> getSemester(final int pageNum, final int pageSize, final String keyWord, final int role){
        StringBuilder sql = new StringBuilder("SELECT ");
        StringBuilder sqlCount = new StringBuilder("SELECT ");


        if (role == 1) {
            log.debug("role == 1");
            // 学生查询群组ＩＤ、课程代码、学期、课程ＩＤ、群组名称、系部名称、系部ＩＤ、学年　from 学生表
            // 内联 学生选课表
            // 内联 课程群组表
            // 内联 课程ID、课程代码对照表
            // 左联 系部表
            // 左联 课程安排表
            // where 学生ID = 登录用户ID
            sql.append("cg.id AS groupId, cg.sCode AS courseCode, ca.tiTerm AS term, cd.iCourseId AS courseId, cg.sName AS groupName," +
                    " dept.sName AS deptName, dept.id AS deptId, ca.sStartYear AS startYear FROM t_student s" +
                    " JOIN t_student_course sc ON s.sNumber = sc.sStudentNum" +
                    " JOIN t_course_group cg  ON cg.sCode = sc.sCourseCode" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " LEFT JOIN t_department dept ON cg.sDeptNum = dept.sNumber" +
                    " LEFT JOIN t_course_arrange ca ON cg.sCode = ca.sCode" +
                    " WHERE s.id = :UserId");
            sqlCount.append("COUNT(cg.id) FROM t_student s" +
                    " JOIN t_student_course sc ON s.sNumber = sc.sStudentNum" +
                    " JOIN t_course_group cg  ON cg.sCode = sc.sCourseCode" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " WHERE s.id = :UserId");
        } else {

            log.debug("role != 1");
            // 老师查询群组ＩＤ、课程代码、学期、课程ＩＤ、群组名称、系部名称、系部ＩＤ、学年　from 老师表
            // 内联 课程群组表
            // 内联 课程ID、课程代码对照表
            // 左联 系部表
            // 左联 课程安排表
            // where 学生ID = 登录用户ID
            sql.append("cg.id AS groupId, cg.sCode AS courseCode, cd.iCourseId AS courseId, cg.sName AS groupName," +
                    " dept.sName AS deptName, dept.id AS deptId, ca.sStartYear as startYear FROM t_teacher t" +
                    " JOIN t_course_group cg  ON cg.sTeacherNum = t.sNumber" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " LEFT JOIN t_department dept ON cg.sDeptNum = dept.sNumber" +
                    " LEFT JOIN t_course_arrange ca ON cg.sCode = ca.sCode" +
                    " WHERE t.id = :UserId");
            sqlCount.append("COUNT(cg.id) FROM t_teacher t" +
                    " JOIN t_course_group cg  ON cg.sTeacherNum = t.sNumber" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " WHERE t.id = :UserId");
        }

        StringBuilder where = new StringBuilder(" AND 1 = 1");
        MapSqlParameterSource param = new MapSqlParameterSource().addValue("UserId", webContext.getUserId());

        //根据关键字模糊搜索
        if (!keyWord.isEmpty()){
            log.debug("根据关键字模糊搜索!keyWord.isEmpty()");
            where.append(" AND (UPPER(cg.sCode) LIKE :search OR UPPER(cg.sName) LIKE :search)");
            param.addValue("search", "%" + keyWord.toUpperCase() + "%");
        }

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        if (total == 0) {
            log.debug("total == 0");
            return new Page<>();
        }

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<CourseGroup> courseGroupList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<CourseGroup>() {
            @Override
            public CourseGroup mapRow(ResultSet resultSet, int i) throws SQLException {
                CourseGroup courseGroup = new CourseGroup();

                courseGroup.setId(resultSet.getLong("groupId")); //群组ID
                courseGroup.setCode(resultSet.getString("courseCode")); //课程代码
                courseGroup.setName(resultSet.getString("groupName")); //群组名称
                courseGroup.setDeptName(resultSet.getString("deptName")); //系部名称
                courseGroup.setDeptId(resultSet.getLong("deptId")); //系部ID
                courseGroup.setYear(resultSet.getString("startYear")); //学年
                courseGroup.setCourseId(resultSet.getLong("courseId")); //课程ID

                return courseGroup;

            }
        });

        log.debug("获取当前课程群组Id、群组名称、课程代码、系部代码");
        return new Page<>(total, pageSize, pageNum,courseGroupList);
    }


    /**
     * 获取历史课程群组列表
     *
     * @param startYear 开始学年
     * @param term 学期
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @return Page<CourseGroup>
     * @author lyj
     * @since 2015-3-31
     */
    public Page<CourseGroup> getHistory(final String startYear, final int term, final int pageNum, final int pageSize, final String keyWord, final int role){
        //获取课程群组ID、课程代码、群组名称、系部代码、系部名称、系部ID
        StringBuilder sql = new StringBuilder("SELECT ");
        StringBuilder sqlCount = new StringBuilder("SELECT ");

        if (role == 1) {
            log.debug("role == 1");
            // 学生查询群组ＩＤ、课程代码、学期、课程ＩＤ、群组名称、系部名称、系部ＩＤ、学年　from 学生表
            // 内联 学生选课表
            // 内联 归档课程群组表
            // 内联 课程ID、课程代码对照表
            // 左联 系部表
            // 左联 课程安排表
            // where 学生ID = 登录用户ID
            sql.append("cg.id AS groupId, cg.sCode AS courseCode, ca.tiTerm AS term,cd.iCourseId AS courseId,cg.sName AS groupName," +
                    " dept.sName AS deptName, dept.id AS deptId, ca.sStartYear AS startYear FROM t_student s" +
                    " JOIN t_student_course sc ON s.sNumber = sc.sStudentNum" +
                    " JOIN t_hcourse_group cg  ON cg.sCode = sc.sCourseCode" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " LEFT JOIN t_department dept ON cg.sDeptNum = dept.sNumber" +
                    " LEFT JOIN t_course_arrange ca ON cg.sCode = ca.sCode" +
                    " WHERE s.id = :UserId");
            sqlCount.append("COUNT(cg.id) FROM t_student s" +
                    " JOIN t_student_course sc ON s.sNumber = sc.sStudentNum" +
                    " JOIN t_hcourse_group cg  ON cg.sCode = sc.sCourseCode" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " LEFT JOIN t_course_arrange ca ON cg.sCode = ca.sCode" +
                    " WHERE s.id = :UserId");
        } else {
            log.debug("role != 1");
            // 老师查询群组ＩＤ、课程代码、学期、课程ＩＤ、群组名称、系部名称、系部ＩＤ、学年　from 老师表
            // 内联 归档课程群组表
            // 内联 课程ID、课程代码对照表
            // 左联 系部表
            // 左联 课程安排表
            // where 学生ID = 登录用户ID
            sql.append("cg.id AS groupId, cg.sCode AS courseCode, ca.tiTerm as term, cd.iCourseId AS courseId, cg.sName AS groupName," +
                    " dept.sName AS deptName, dept.id AS deptId, ca.sStartYear as startYear FROM t_teacher t" +
                    " JOIN t_hcourse_group cg  ON cg.sTeacherNum = t.sNumber" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " LEFT JOIN t_department dept ON cg.sDeptNum = dept.sNumber" +
                    " LEFT JOIN t_course_arrange ca ON cg.sCode = ca.sCode" +
                    " WHERE t.id = :UserId");
            sqlCount.append("COUNT(cg.id) FROM t_teacher t" +
                    " JOIN t_hcourse_group cg  ON cg.sTeacherNum = t.sNumber" +
                    " JOIN t_course_code cd ON cg.sCode = cd.sCode" +
                    " LEFT JOIN t_course_arrange ca ON cg.sCode = ca.sCode" +
                    " WHERE t.id = :UserId");
        }

        StringBuilder where = new StringBuilder(" AND 1 = 1");
        MapSqlParameterSource param = new MapSqlParameterSource().addValue("UserId", webContext.getUserId());

        //根据关键字模糊搜索
        if (!keyWord.isEmpty()){
            log.debug("根据关键字模糊搜索!keyWord.isEmpty()");
            where.append(" AND (UPPER(ca.sStartYear) LIKE :search OR UPPER(cg.sCode) LIKE :search OR UPPER(cg.sName) LIKE :search)");
            param.addValue("search", "%" + keyWord.toUpperCase() + "%");
        }

        //过滤开始学年
        if (!startYear.isEmpty() && !startYear.equals("0")){
            log.debug("过滤开始学年!startYear.isEmpty() && !startYear.equals(\"0\")");
            where.append(" AND ca.sStartYear = :startYear ");
            param.addValue("startYear", startYear);
        }

        if (term == 0 || term == 1){
            log.debug("term == 0 || term == 1");
            where.append(" AND ca.tiTerm = :term ");
            param.addValue("term", term);
        }

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        if (total == 0) {
            log.debug("total == 0");
            return new Page<>();
        }

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<CourseGroup> courseGroupList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<CourseGroup>() {
            @Override
            public CourseGroup mapRow(ResultSet resultSet, int i) throws SQLException {
                CourseGroup courseGroup = new CourseGroup();

                courseGroup.setId(resultSet.getLong("groupId")); //群组ID
                courseGroup.setCode(resultSet.getString("courseCode")); //课程代码
                courseGroup.setName(resultSet.getString("groupName")); //群组名称
                courseGroup.setDeptName(resultSet.getString("deptName")); //系部名称
                courseGroup.setDeptId(resultSet.getLong("deptId")); //系部ID
                courseGroup.setYear(resultSet.getString("startYear")); //学年
                courseGroup.setCourseId(resultSet.getLong("courseId"));
                courseGroup.setTerm(resultSet.getInt("term")); //学期

                return courseGroup;

            }
        });

        log.debug("获取历史课程群组列表");
        return new Page<>(total, pageSize, pageNum,courseGroupList);
    }


    /**
     * 根据群组ID获取负责人头像、姓名
     *
     * @param groupId 群组ID
     * @return Long
     * @author lyj
     * @since 2015-3-31
     */
    public Teacher getLeaderId(final long groupId){
        StringBuilder sql = new StringBuilder("SELECT id,sName,sPicture FROM t_teacher WHERE sNumber = (SELECT sTeacherNum FROM t_course_group WHERE id = :groupId)");

        try{
            log.debug("根据群组ID获取负责人头像、姓名");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("groupId", groupId), new RowMapper<Teacher>() {
                @Override

                public Teacher mapRow(ResultSet resultSet, int i) throws SQLException {
                    Teacher teacher = new Teacher();

                    teacher.setId(resultSet.getLong("id"));
                    teacher.setName(resultSet.getString("sName"));
                    teacher.setPicture(resultSet.getString("sPicture"));

                    return teacher;
                }
            });

        } catch (EmptyResultDataAccessException ex){
            log.debug("获取课程群组负责人的ID失败");
            log.warn("获取课程群组负责人的ID失败！groupId->{}", groupId);
            return null;
        }
    }


    /**
     * 获取历史学期下拉列表
     *
     * @return List
     * @author lyj
     * @since 2015-3-31
     */
    public List<Integer> getStartYear(final String userNum) {
        StringBuilder sql = new StringBuilder("SELECT ");

        if (webContext.getRole() == Roles.STUDENT) {
            log.debug("webContext.getRole() == Roles.STUDENT");
            // 学生查询历史学年 from 学生表
            // 内联 学生选课表
            // 内联 归档课程群组表
            // 内联 课程安排表
            // where 学生ID = 登录用户ID
            sql.append(" DISTINCT ca.sStartYear FROM t_course_arrange ca" +
                    " JOIN t_student_course sc ON sc.sCourseCode = ca.sCode" +
                    " JOIN t_hcourse_group hg ON sc.sCourseCode = hg.sCode" +
                    " WHERE sc.sStudentNum = :UserNum");
        } else {
            log.debug("webContext.getRole() != Roles.STUDENT");
            // 老师查询历史学年 from 老师表
            // 内联 归档课程群组表
            // 内联 课程安排表
            // where 学生ID = 登录用户ID
            sql.append(" DISTINCT ca.sStartYear FROM t_course_arrange ca " +
                    "JOIN t_hcourse_group hg ON ca.sCode = hg.sCode WHERE hg.sTeacherNum = :UserNum");
        }

        log.debug("获取历史学期下拉列表");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("UserNum", userNum), Integer.class);
    }

    /**
     * 教师所属课程群组列表
     * @param teacherNum 工号
     * @return List
     *
     * @author fnaruiping
     * @since 2015-04-06
     */
    public List<CourseGroup> getListForTeacher(String teacherNum){
        String sql = "SELECT id,sCode,sName FROM t_course_group WHERE sTeacherNum=:teacherNum";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("teacherNum", teacherNum), new RowMapper<CourseGroup>() {
            @Override
            public CourseGroup mapRow(ResultSet rs, int i) throws SQLException {
                CourseGroup courseGroup = new CourseGroup();

                courseGroup.setId(rs.getLong("id"));
                courseGroup.setCode(rs.getString("sCode"));
                courseGroup.setName(rs.getString("sName"));

                return courseGroup;
            }
        });
    }


    /**
     * 学生所属课程群组列表
     * @param studentNum 学号
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<CourseGroup> getListForStudent(String studentNum){
        String sql = "SELECT c.id,c.sCode,c.sName FROM t_course_group c LEFT JOIN t_student_course s " +
                "ON c.sCode=s.sCourseCode WHERE s.sStudentNum=:studentNum";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("studentNum", studentNum), new RowMapper<CourseGroup>() {
            @Override
            public CourseGroup mapRow(ResultSet rs, int i) throws SQLException {
                CourseGroup courseGroup = new CourseGroup();

                courseGroup.setId(rs.getLong("id"));
                courseGroup.setCode(rs.getString("sCode"));
                courseGroup.setName(rs.getString("sName"));

                return courseGroup;
            }
        });
    }

    /**
     * 获取所有课程群组
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<CourseGroup> getAll() {
        String sql = "SELECT id,sCode,sName FROM t_course_group";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource(), new RowMapper<CourseGroup>() {
            @Override
            public CourseGroup mapRow(ResultSet rs, int i) throws SQLException {
                CourseGroup courseGroup = new CourseGroup();

                courseGroup.setId(rs.getLong("id"));
                courseGroup.setCode(rs.getString("sCode"));
                courseGroup.setName(rs.getString("sName"));

                return courseGroup;
            }
        });
    }

    /**
     * 获取与数据库中重复的课程代码
     * @param courseGroupCodeSet 课程群组课程代码集合
     * @return String
     *
     * @author wyh
     * @since 2015-03-26
     */
    public String getDuplicateCode(final Set<String> courseGroupCodeSet) {
        if (courseGroupCodeSet.isEmpty()) {
            log.debug("导入的课程群组课程代码集合为空");

            log.info("课程群组课程代码集合为0");
            return null;
        }

        try {
            String sql = "SELECT sCode FROM t_course_group WHERE sCode IN (:numSet) LIMIT 1";
            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", courseGroupCodeSet);

            log.debug("return 表中与导入数据重复的课程群组课程代码");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("数据库中无重复的课程群组课程代码");

            log.info("数据库中无重复的课程群组课程代码");
            return null;
        }
    }

    /**
     * 批量保存课程群组
     * @param courseGroupList 课程群组List
     * @return int[]
     *
     * @author wyh
     * @since 2015-03-26
     */
    public int[] saveCourseGroups(final List<CourseGroup> courseGroupList){
        String sql = "INSERT INTO t_course_group VALUES(NULL, :code, :name, :deptNum, :teacherNum)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(courseGroupList.toArray());
        log.debug("return 批量导入课程群组数据的结果");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 批量删除课程群组
     * @return int
     *
     * @author wyh
     * @since 2015-03-26
     */
    public int deleteAll(){
        String sql = "DELETE FROM t_course_group";

        log.debug("return 删除课程群组数据的结果");
        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource());
    }

    /**
     * 判断学生是否在群组里,返回群组课程代码
     * @param studentId 学生ID
     * @param groupId 群组ID
     * @return bool
     *
     * @author wyh
     * @since 2015-03-26
     */
    public boolean checkStudentInGroup(long studentId, long groupId, StringBuilder courseCode, StringBuilder teacherNum){
        String sql = "SELECT cg.sCode, cg.sTeacherNum FROM t_student s, t_student_course sc, t_course_group cg WHERE s.id=:studentId AND cg.id=:groupId AND s.sNumber=sc.sStudentNum AND sc.sCourseCode=cg.sCode";

        try {
            Map<String, Object> resultMap = namedParameterJdbcTemplate.queryForMap(sql, new MapSqlParameterSource().addValue("studentId", studentId).addValue("groupId", groupId));
            courseCode.append((String)resultMap.get("sCode"));
            teacherNum.append((String)resultMap.get("sTeacherNum"));

            log.debug("返回 学生属于群组");
            return true;
        } catch (EmptyResultDataAccessException ex){
            log.debug("学生不在群组");
            log.warn("查询记录不存在：studentId->{}, groupId->{}", studentId, groupId);
            return false;
        }
    }

    /**
     * 课程群组列表
     * @param depart 系部编码
     * @param page 页码
     * @param pageSize 页数
     * @return Page
     *
     * @author wyh
     * @since 2015-03-30
     */
    public Page<CourseGroup> listGroup(String depart, int page, int pageSize){
        StringBuilder sql = new StringBuilder("SELECT cg.*, t.sName sTeacher, d.sName sDept FROM t_course_group cg LEFT JOIN t_teacher t ON t.sNumber=cg.sTeacherNum LEFT JOIN t_department d ON d.sNumber=cg.sDeptNum ");
        StringBuilder sqlTotal = new StringBuilder("SELECT count(*) FROM t_course_group cg ");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        //筛选系部编码
        if(!StringUtils.isEmpty(depart)){
            log.debug("系部不为空：!StringUtils.isEmpty(depart)");
            sql.append(" WHERE cg.sDeptNum=:depart ");
            sqlTotal.append(" WHERE cg.sDeptNum=:depart ");
            sps.addValue("depart", depart);
        }

        //查询总数
        int total = namedParameterJdbcTemplate.queryForObject(sqlTotal.toString(), sps, Integer.class);
        if(total == 0){
            log.debug("查询总数为0：total == 0");
            return new Page<>();
        }

        //limit
        sql.append(getLimitStr(page, pageSize));

        List<CourseGroup> groupList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<CourseGroup>() {
            @Override
            public CourseGroup mapRow(ResultSet resultSet, int i) throws SQLException {
                CourseGroup cg = new CourseGroup();
                cg.setId(resultSet.getLong("id"));
                cg.setCode(resultSet.getString("sCode"));
                cg.setName(resultSet.getString("sName"));
                cg.setTeacher(resultSet.getString("sTeacher"));
                cg.setDepart(resultSet.getString("sDept"));
                return cg;
            }
        });
        log.debug("返回 当前课程群组列表");
        return new Page<>(total, pageSize, page, groupList);
    }


	/**
     * 查询学生当前学期的课程群组ID集合
     *
     * @param studentNum 学生学号
     * @return List
     * @author cx
     * @since 2015/03/19
     */
    public List<Long> getIdsForStudent(final String studentNum) {
        StringBuilder sql = new StringBuilder("SELECT cg.id").append(" FROM t_course_group cg INNER JOIN t_student_course sc")
                .append(" ON cg.sCode=sc.sCourseCode AND sc.sStudentNum=:studentNum");

        SqlParameterSource sps = new MapSqlParameterSource().addValue("studentNum", studentNum);

        log.debug("查询学生当前学期的课程群组ID集合");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), sps, Long.class);
	}

    /**
     * 获取指定教师负责的课程群组代码集合
     * @param teacherNum 教师编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-28
     */
    public List<String> getCodesByTeacher(String teacherNum){
        String sql = "SELECT sCode FROM t_course_group WHERE sTeacherNum=:teacherNum";


        log.debug("获取指定教师负责的课程群组代码集合");
        return namedParameterJdbcTemplate.queryForList(sql,new MapSqlParameterSource().addValue("teacherNum",teacherNum),String.class);
    }


    /**
     * 根据课程代码获取该课程下所有课程群组的负责人ID
     * @param courseCodes 课程代码
     * @return List<Long>
     * @author lyj
     * @since 2015-5-6
     */
    public List<Long> getTeacherIdsByCourseCode(final Set<String> courseCodes) {
        StringBuilder sql = new StringBuilder("SELECT te.id FROM t_course_group cg  JOIN t_teacher te ON cg.sTeacherNum = te.sNumber WHERE cg.sCode IN (:codes)");

        log.debug("根据课程代码获取该课程下所有课程群组的负责人ID");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("codes", courseCodes), Long.class);
    }

    /**
     * 查询教师当前学期的课程群组ID集合
     * @param teacherNum 教师工号
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-07
     */
    public List<Long> getIdsForTeacher(final String teacherNum) {
        String sql = "SELECT id FROM t_course_group WHERE sTeacherNum=:teacherNum";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("teacherNum", teacherNum);

        return namedParameterJdbcTemplate.queryForList(sql, sps, Long.class);
    }


    /**
     * 根据number、groupId判断登录用户是否属于该课程群组
     *
     * @param groupId 群组ID
     * @param number 工号/学号
     * @return int
     * @author lyj
     * @since 2015-5-13
     */
    public int checkGroupIdIsNum(final long groupId, final String number) {
        StringBuilder sql = new StringBuilder();
        if (webContext.getRole() == Roles.STUDENT) {
            sql.append("SELECT" +
                    " (SELECT COUNT(cg.id) FROM t_course_group cg JOIN t_student_course sc ON cg.sCode=sc.sCourseCode AND sc.sStudentNum=:number AND cg.id = :id) countCourseGroup," +
                    " COUNT(cg.id) AS countHCourseGroup FROM t_hcourse_group cg JOIN t_student_course sc ON cg.sCode=sc.sCourseCode AND sc.sStudentNum=:number AND cg.id = :id");
        } else {
            sql.append("SELECT" +
                    "(SELECT COUNT(cg.id) FROM t_course_group cg WHERE cg.id = :id AND cg.sTeacherNum = :number) countCourseGroup," +
                    " COUNT(hcg.id) AS countHCourseGroup FROM t_hcourse_group hcg WHERE hcg.id = :id AND hcg.sTeacherNum = :number");
        }

        Map<String, Object> map = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource()
                .addValue("id", groupId)
                .addValue("number", number), new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, Object> reMap = new HashMap<String, Object>();

                reMap.put("countCourseGroup", resultSet.getInt("countCourseGroup"));
                reMap.put("countHCourseGroup", resultSet.getInt("countHCourseGroup"));

                return reMap;
            }
        });

        return (int) map.get("countCourseGroup") + (int) map.get("countHCourseGroup");

    }
}
